Data Flow Task Problem: ssis

Showing posts with label ssis. Show all posts

Sunday, March 25, 2012

Data encyption using symmetric keys outside SQL Server

Hello. I have a problem that spans VB.net, SQL Server and SSIS but is rooted in the need to encrypt column data in SQL Server.

I would like to encrypt data that I am bringing into SQL Server in the Data transformation script component of an SSIS package. I have achieved this but I can't decrypt the data because the keys don't match. I would like to use symmetric key encryption but I don't see how to get the symmetric key that I created in SQL Server available to the VB.net script component in SSIS.

Please advise me if my approach is correct and what steps I need to take.

Importing or exporting key material for SYMMETRIC KEYs is not supported in SQL Server 2005. SYMMETRIC KEY material is always encrypted in the database and we don’t have any access point where we display such material in an unprotected form for security reasons, because of this SYMMETRIC KEYS as well as ciphertext created by EncryptByKey are only meant to be consumed by SQL Server.

-Raul Garcia

SDE/T

SQL Server Engine

|||Thank you for the response. I suspected as much for the very reasons you mentioned.
I did some work on asymetric keys but wasn't successful. Can you tell me the correct strategy to expose the public key so I can use it to encrypt within the SSIS package.|||

Here is a link that should be useful. In this link the author was also using ASYMMETRIC KEYS in SQL Server and VB .Net:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information will be useful,but let us know if there is anything else we can do to help.

-Raul Garcia

SDE/T

SQL Server Engine

Monday, March 19, 2012

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

One quick question - how is the data going to be used once it has been imported? To store data in the format that you specified then you'll have to decare the column as a text-based datatype, such as VARCHAR, which is not necessarily the best option for numerical data.

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data converted when loaded into SQL 2k5 table

Hi All

Data in access is converted when loaded into SQL 2k5

00 >> 0

01 >> 1

03 >> 3

I am using SSIS import wizard to load data from MS Access ’03 into a SQL 2k5 database.Some data of the data is converted, or the leading zero is deleted when loaded into sql table.

The data type on the source field is byte with a 00 format. The data type in 2k5 is tinyint.

I need some help with getting the data to load into 2k5 exactly as it appears in access.

Thanks for you help.

Nats

Hi Nats

If you're going to perform calculations on the data then it might be better to store the data as TINYINT then manipulate the formatting when you want to return / display the data.

e.g.

DECLARE @.int INT

SET @.int = 1

SELECT '00' + RIGHT(CAST(@.int AS VARCHAR(1)), 2)

...will return a text string of '01' even though @.int is of type integer.

Chris

|||

I don't think it will be used in any calculation.

Thanks,

Data conversion inserting to DB2 on AS400 with SSIS

I created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.

When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.

For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as '?' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.

Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.

Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?

Thanks in advance for any help!

Try to set the appropriate LocaleID and DefaultCodePage on your destination component.

Thanks.

|||

The LocaleID and DefaultCodePage were set correctly.

What I found was in the Data Link Properties of connection manager, the "Host CCSID" was set to "OEM - United States [437]". I changed it to "EBCDIC - US/Canada [37]" and it started working perfectly.

Thank you for your suggestion though.

Data Conversion in SSIS

what is the use of Data Conversion

please give me an example

BOL: "The Data Conversion transformation converts the data in an input column to a different data type and then copies it to a new output column. For example, a package can extract data from multiple sources, and then use this transformation to convert columns to the data type required by the destination data store. You can apply multiple conversions to a single input column. "

For example, I used it when the DataSource date column was treated as a string , but I needed it to be converted to Datatime , or input column was INT datatype, but I needed to populate BIGINT destination column.

Data Conversion failed due to Potential Loss of data

Hi,

I am getting this error when my ssis package is running

Data Conversion failed due to Potential Loss of data

the input column is in string format and output is in sql server bigint

the error is occuring when there is an empty string in the input. what should i do to overcome this

It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.

Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

|||

desibull wrote:

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

NULL and "empty string" are two very different things.

To expand on what I suggested earlier and desibull's code above:

ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]

ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]

Sunday, March 11, 2012

Data Conversion Components & Code Page issue

I am using the SSIS wizard to pull data from DB2 z/os to sql server. The data flow task that is created converts the data to DT_STR Ansi 1252 before storing to sql server database. The package is blowing up on in the data conversion component...no match in found in target code page...for my city name field.

My old dts wizard didn't have this problem. The forums seem to indicate that SSIS is no longer doing some of the implicit conversions that DTS did and I may have to do more than one conversion.

What format type/code page do I use for the other conversion? The code page for my DB2 data source is 37.

I've tried several scenarios and none of them have worked. Any hints?

Quick and dirty work around was to change the destination column to nchar.|||

This one is a good workaround if you don't mind your strings being Unicode at the destination.

If you would rather keep your ANSI strings, then you should go to the package set the appropriate code page in the data conversion transform and set the appropriate collation in the CREATE TABLE statement.

Thanks.

|||

I tried that and couldn't get it to work.

My problem isn't a foreign language issue, but a special character issue. My old DTS packages accepted the special characters without a fuss but I'm told SSIS isn't doing the implicit conversion that the old DTS use to do.

Obviously, I can't seem to figure out which code page to use. And where do I set all of these code pages? My input data is coming from DB2 z/OS which is using code page 37. Do I set the code page of the source component to 37? The destination? The transform component? I tried setting the code page with multiple scenarios...none worked.

There is no where that I can find that explains how to do a code page transformation except in the most general terms.

PS. Wouldn't I only change the collation for a foreign language?

Data Conversion

I need help!!!! I am about to go nuts! I am getting the following error in SSIS:

Error at Violations Load [SQL Server Destination [3800]]: The column ""Site No "" can't be inserted because the conversion between types DT_STR and DT_NUMERIC is not supported.

I have tried using the data conversion task, modifying all properties to DT_NUMERIC and so on. I just can't figure it out! I am attempting to load a numeric field from a flat file into a SQL Server database. I cannot find any information on this and have tried about everything. I need any help or suggestions anyone can offer! Thank you in advance for your help!!

If the column in sql is a string (char, varchar, etc) you should use a (DT_WSTR,<<length>>)intcolum from file.

For example.. (DT_WSTR,2)12 would cast the number 12 into a string of 2 character length as "12"

Hope that helps.

|||

The destination component tells you the type of the target table. Double click on any data path (the green lines between the components) to see teh tye of the field in the pipeline. At some point the 2 will be different. Thats where you need to do a data conversion.

-Jamie

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

Data contained in a CDATA Section in XML is lost

I know that anything in a CDATA section will be ignored by an XML parser. Does that hold true for the SSIS XML Source?

I am trying to import a large quantity of movie information and all of the reviews, synopsis, etc are contained in CDATA. example:

Sounds like a good one, no?

The record gets inserted into the database however it contains a NULL in the field for the synopsis text. I would imagine that the reason for this would fall at the feet of CDATA's nature and that SSIS is ignoring it.

Any thoughts would be appreciated. Thanks.

Yes, this is a known issue in RTM (due to a bug in the reader XMLSource adapter calls behind the scene), it should be fixed in SP1 already. Please let us know if you still see issues after applying SP1.

Thanks

Wenyang

Data contained in a CDATA Section in XML is lost

I know that anything in a CDATA section will be ignored by an XML parser. Does that hold true for the SSIS XML Source?

I am trying to import a large quantity of movie information and all of the reviews, synopsis, etc are contained in CDATA. example:

Sounds like a good one, no?

Any thoughts would be appreciated. Thanks.

Thanks

Wenyang

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know?

Thanks in advance,

Jerome Smith

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Friday, February 24, 2012

Dangerous Bug in SSIS?

Hi,

I recently seemed to have found a major bug in SSIS packages...
Well i was just showing the new SSIS to a few work collegues when i suddenly decided to show how cool it was to be able to minimize and maximize tasks containers.

Well the bug just showed up when i maximized the container again and there it was... the Conditional precedences where gone... no more visible..

Everyone is experiencing the same problem? Solutions?
Best Regards,
Luis Sim?es

Luis,
If you try and recreate the constraints it won't let you because it says the constraints already exist.

So yes, there is a bug - but I would hardly call it major. It doesn't stop the package from running successfully.

Log it at the feedback centre: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||And how do i change the conditions now?

The precedences are invisible... that's bad... What if someone else must use the package?

Best Regards,|||

I had the same issue with tasks inside a sequence container. The workflow arrows appeared to be missing, but they were still there, just invisible. I got them to reappear by minimizing and then expanding the sequence container.

Not a dangerous bug, just an annoying one.

Dangerous Bug in SSIS?

Hi,

Well the bug just showed up when i maximized the container again and there it was... the Conditional precedences where gone... no more visible..

Everyone is experiencing the same problem? Solutions?
Best Regards,
Luis Sim?es

Luis,
If you try and recreate the constraints it won't let you because it says the constraints already exist.

So yes, there is a bug - but I would hardly call it major. It doesn't stop the package from running successfully.

Log it at the feedback centre: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||And how do i change the conditions now?

The precedences are invisible... that's bad... What if someone else must use the package?

Best Regards,|||

Not a dangerous bug, just an annoying one.

Dangerous Bug in SSIS?

Hi,

Well the bug just showed up when i maximized the container again and there it was... the Conditional precedences where gone... no more visible..

Everyone is experiencing the same problem? Solutions?
Best Regards,
Luis Sim?es

Luis,
If you try and recreate the constraints it won't let you because it says the constraints already exist.

So yes, there is a bug - but I would hardly call it major. It doesn't stop the package from running successfully.

Log it at the feedback centre: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

|||And how do i change the conditions now?

The precedences are invisible... that's bad... What if someone else must use the package?

Best Regards,|||

Not a dangerous bug, just an annoying one.

Sunday, February 19, 2012

Daily Import of file with date

Hello,

I have an SSIS package that imports daily files that have the date in the file name. I'd like my SSIS package to pickup today's file and run the package.

I've been using expression builder to create the file name but it drops the leading zeros in the month and the day.

My Import file looks like this: SRMSNotes_DATA_20080118.txt

Using this code: "D:\\importdata\\srmsdata\\SRMSNotes_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

SSIS looks for this file: SRMSNotes_DATA_2008118.txt

How do I make SSIS add the leading zeros?

Thanks for you help"D:\\importdata\\srmsdata\\SRMSNotes_DATA_" +
(DT_WSTR, 4) YEAR( GETDATE())

+
((DATEPART("mm",GETDATE()) ) < 10 ? ("0" + (DT_WSTR, 2)MONTH( GETDATE())) : (DT_WSTR, 2)MONTH( GETDATE()))

+
(DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

You'd need to apply something similair for the DAY part|||To Get this:
20080213004235
YYYYMMDDHHMMSS

I use:
(DT_WSTR, 4) YEAR(GETDATE())
+
RIGHT(("0" + (DT_WSTR, 2) MONTH( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DAY( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("hh", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("n", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("s", GETDATE())),2)

To Get this:
20080213004235
YYYYMMDDHHMMSS

Daily build - trying to deploy from the command line

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Daily build - trying to deploy from the command line

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Tuesday, February 14, 2012

Cutting & Pasting Tasks

Has anyone else experienced serious pain when cutting and pasting tasks between packages? In that, the default way SSIS lays out the tasks is absolutely atrocious (things on top of each other, way to far apart from each other, boxes sized to small, etc.) Is there some trickery I need to do to get it to retain the layout and formatting when I paste?
Its rubbish isn't it? I also hate the way auto-format can sometimes produce a worse layout than what you started with and there's no way of CTRL-Z-ing it.

Sorry, that's not very constructive is it? I've fed this back to MS and am hoping its on their list of things for Vnext.

For the time being Greg, I think you're stuck with it.

-Jamie

P.S. CTRL-Z is something I'd like to see across the board but I dare say you need Visual Studio hooks in order to do this - so its a VS problem!

Sunday, March 25, 2012

Data encyption using symmetric keys outside SQL Server

Monday, March 19, 2012

Data converted when loaded into SQL 2k5 table

Data converted when loaded into SQL 2k5 table

Data conversion inserting to DB2 on AS400 with SSIS

Data Conversion in SSIS

Data Conversion failed due to Potential Loss of data

Sunday, March 11, 2012

Data Conversion Components & Code Page issue

Data Conversion

Data Conversion

Data contained in a CDATA Section in XML is lost

Data contained in a CDATA Section in XML is lost

Data Connections: Where the hell are they stored?!!

Data Connections: Where the hell are they stored?!!

Friday, February 24, 2012

Dangerous Bug in SSIS?

Dangerous Bug in SSIS?

Dangerous Bug in SSIS?

Sunday, February 19, 2012

Daily Import of file with date

Daily build - trying to deploy from the command line

Daily build - trying to deploy from the command line

Tuesday, February 14, 2012

Cutting & Pasting Tasks

Data Flow Task Problem

Blog Archive

About Me