Need basic guidance on massive access to sql conversion

I have a rather daunting task ahead of me. I have ot take an access mdb and bring it into sql express. Converting the tables is not a biggie, but I have more than 2500 queries that need to be brought over. I have several questions.

1. In many places I use queries with parameters as views. For example I might have a query that takes a couple of parameters and then turn around write another query that uses the first query as a table. Is there a way to do parameterized views in Sql?

2. Does anyone know of a decent automated tool to do this? The upsizing wizard is useless and I tried teh sql migration assistant, and it doesn't convert queries that have parameters.

3. Has anyone done anything on this kind of scale before?

Thanks ... Ed

1. The closest to parameterized views is using Table Valued Functions. Check in Books Online about using TVFs.

2. No 'decent tools' known. Parameterized views can be 'troublesome'.

3. Yes, and it will take a lot of dilligent and concentrated effort.

and 4. Good Luck -because you WILL need it!

Need Assistance with Dynamic Properties and FTP Task

Hello,

I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:

CDNSC.CDNSC.SC00015.04012007

The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.

My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.

What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?

Thank you for your help!

cdun2On Apr 4, 5:35 am, "cdun2" <ChrisDunnM...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I am building a packge where an FTP task needs to pull down a single
file
every day from a specific location. The location will only have the
one file.
The file name will be different every day. A sample of one of the file
names
looks like this:
>
CDNSC.CDNSC.SC00015.04012007
>
The file names will be different every day, as the last eight digits
represent the date of the data in the file. The source files will be
located
in a subdirectory called 'outgoing'.
>
My first approach to this has been to use a Dyamic Properties Task to
set
the SourceFileName of the FTP Task. I tried using a sting Global
Variable
expressed as *.*.*.* thinking a wildcard would work, but it didn't.
>
What approaches can I take so that the Source Filename in the FTP task
will
dymanically update each day to get the one file?
>
Thank you for your help!
>
cdun2

I wound up creating a script solution that was pretty straight
forward. I basically set up string variables using DATE to get the
data portion of the file name, concatenated that to the first part of
the filename, and set the whole string equal to the SourceFileName of
the FTP Task. It seems to work fine.

cdun2sql

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.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 [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. 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 [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.

Need assistance with Data Flow Task error - please help

I am trying to execute a Data Flow Task. There is only one task in the data flow task so far, and that is a Flat File Source task. I also set up a Connection Manager for the file, myfile.txt.

When I try to run the Data Flow Task, I get these errors:

The first problem seems to be with the connection, on the first line. What connection element is missing here? The others seem to be truncation errors. I don't know why that is. I changed the column sizes to match the sizes of database columns. They ARE surrounded by double-quotes, but I have {"} specified as a Text Qualifier in my Connection Manager.

Please, need help!

Error: 0xC001000E at : The connection "{F6513CB9-29E2-4D88-A86B-0EF9DFABE9D6}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
SSIS package "MyPackage.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 [2168]: The processing of file "D:\myfile.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Data Flow Task, Flat File Source [2168]: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, Flat File Source [2168]: The "output column "Column 0" (2185)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (2185)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Data Flow Task, Flat File Source [2168]: An error occurred while processing file "D:\myfile.txt" on data row 1.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (2168) returned error code 0xC0202092. 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 [2168]: The processing of file "D:\myfile.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task

One of the error messages indicates that the Flat File source will fail when the data in column 0 is truncated. Have you tried setting the source to not fail when data is truncated?

1. Right-click the Flat File Source component on the Data Flow tab in SSIS, and then click Edit.

2. Click Error Output in the left-hand pane.

3. In the Truncation drop-down list, select Ignore failure.

4. Click OK.

Also, if you're configuring the Flat File Connection Manager to use a specified code page (see Error 0xC02020A1 above), try configuring the Connection Manager for Unicode:

1. Right-click the Flat File Connection Manager, and then click Edit.

2. Select the Unicode checkbox.

|||

Actually i was having the same rpoblem and i was playing with diferent options.

Go to your Flat File Source in Connection Manager

Go to Advanced and verify outputColumn width and match with your table datatype / the size of data you have it on file

make it sure you donot want to ignore the data just becasue of some errors .

Satish Shrikhande

satish_isi@.hotmail.com

|||Yeah, also watch out for NULL (hex 00) characters in your string fields. NULLs terminate DT_STR and DT_WSTR data types -- even if there is more data after the NULL character.

Need a task that will zip a file prior to upload via FTP

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.id gues you need a 3rd party piece of software for this. we have used PKZip exe here. its a simple command line app, so you can easily call in in a script task and get it to do its thing. there could be an easier way though...|||

We have written a SSIS Task to do this for you. It works rather nicely.

-Jamie

|||

Excellent! Thanks. Will you be posting a link to it on your blog?

Cheers

|||

No I'm afraid not because it is Conchango's own intellectual property. I was more making the point that this can be done.

Don't fret however, Darren tells me that he has built his own so look for it appearing on SQLIS.com soon enough.

-Jamie

|||Cool, thanks. I'll be looking for it to make its debut there.|||Is there an eta for this?|||I haven't found a task for this on sqlis.com or anywhere else yet, have you?|||

Al C. wrote:

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68|||

Or use this: http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

-Jamie

|||Thanks. It looks like that task compresses into gzip format. I need the same format as when you do a right-click > 'Send to compressed (zipped) folder' via Windows since we send the file to clients who expect that format.|||

Duane Douglas wrote:

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Thanks. I posted the suggestion.

|||

Hi

I am also looking for the same task, what solution have you adopted in answer of your problem?

|||We purchased WinZip product with command line interface. I added an Execute Process task to run the WinZip and I pass the file name in the command line arguments. There are other products that can do the same I believe, such as PKZip.|||We have built task for archiving in Zip, GZip, BZip2, Unix, Tar. We also have task for secure FTP (SFTP). You may give it a try here: http://www.cozyroc.com/

Regards,
Ivan

Need a task that will zip a file prior to upload via FTP

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.id gues you need a 3rd party piece of software for this. we have used PKZip exe here. its a simple command line app, so you can easily call in in a script task and get it to do its thing. there could be an easier way though...|||

We have written a SSIS Task to do this for you. It works rather nicely.

-Jamie

|||

Excellent! Thanks. Will you be posting a link to it on your blog?

Cheers

|||

No I'm afraid not because it is Conchango's own intellectual property. I was more making the point that this can be done.

Don't fret however, Darren tells me that he has built his own so look for it appearing on SQLIS.com soon enough.

-Jamie

|||Cool, thanks. I'll be looking for it to make its debut there.|||Is there an eta for this?|||I haven't found a task for this on sqlis.com or anywhere else yet, have you?|||

Al C. wrote:

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68|||

Or use this: http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

-Jamie

|||Thanks. It looks like that task compresses into gzip format. I need the same format as when you do a right-click > 'Send to compressed (zipped) folder' via Windows since we send the file to clients who expect that format.|||

Duane Douglas wrote:

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Thanks. I posted the suggestion.

|||

Hi

I am also looking for the same task, what solution have you adopted in answer of your problem?

|||We purchased WinZip product with command line interface. I added an Execute Process task to run the WinZip and I pass the file name in the command line arguments. There are other products that can do the same I believe, such as PKZip.|||We have built task for archiving in Zip, GZip, BZip2, Unix, Tar. We also have task for secure FTP (SFTP). You may give it a try here: http://www.cozyroc.com/

Regards,
Ivan

Need a task that will zip a file prior to upload via FTP

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.id gues you need a 3rd party piece of software for this. we have used PKZip exe here. its a simple command line app, so you can easily call in in a script task and get it to do its thing. there could be an easier way though...|||

We have written a SSIS Task to do this for you. It works rather nicely.

-Jamie

|||

Excellent! Thanks. Will you be posting a link to it on your blog?

Cheers

|||

No I'm afraid not because it is Conchango's own intellectual property. I was more making the point that this can be done.

Don't fret however, Darren tells me that he has built his own so look for it appearing on SQLIS.com soon enough.

-Jamie

|||Cool, thanks. I'll be looking for it to make its debut there.|||Is there an eta for this?|||I haven't found a task for this on sqlis.com or anywhere else yet, have you?|||

Al C. wrote:

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68|||

Or use this: http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

-Jamie

|||Thanks. It looks like that task compresses into gzip format. I need the same format as when you do a right-click > 'Send to compressed (zipped) folder' via Windows since we send the file to clients who expect that format.|||

Duane Douglas wrote:

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Thanks. I posted the suggestion.

|||

Hi

I am also looking for the same task, what solution have you adopted in answer of your problem?

|||We purchased WinZip product with command line interface. I added an Execute Process task to run the WinZip and I pass the file name in the command line arguments. There are other products that can do the same I believe, such as PKZip.|||We have built task for archiving in Zip, GZip, BZip2, Unix, Tar. We also have task for secure FTP (SFTP). You may give it a try here: http://www.cozyroc.com/

Regards,
Ivan

Need a task that will zip a file prior to upload via FTP

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.id gues you need a 3rd party piece of software for this. we have used PKZip exe here. its a simple command line app, so you can easily call in in a script task and get it to do its thing. there could be an easier way though...|||

We have written a SSIS Task to do this for you. It works rather nicely.

-Jamie

|||

Excellent! Thanks. Will you be posting a link to it on your blog?

Cheers

|||

No I'm afraid not because it is Conchango's own intellectual property. I was more making the point that this can be done.

Don't fret however, Darren tells me that he has built his own so look for it appearing on SQLIS.com soon enough.

-Jamie

|||Cool, thanks. I'll be looking for it to make its debut there.|||Is there an eta for this?|||I haven't found a task for this on sqlis.com or anywhere else yet, have you?|||

Al C. wrote:

I have a text file I need to zip (compress) before uploading it via an FTP task. Do I have to use a third-party tool to do this? Thanks.

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68|||

Or use this: http://www.thejoyofcode.com/SSIS_Compress_File_Task.aspx

-Jamie

|||Thanks. It looks like that task compresses into gzip format. I need the same format as when you do a right-click > 'Send to compressed (zipped) folder' via Windows since we send the file to clients who expect that format.|||

Duane Douglas wrote:

you can suggest that this feature be added here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

Thanks. I posted the suggestion.

|||

Hi

I am also looking for the same task, what solution have you adopted in answer of your problem?

|||We purchased WinZip product with command line interface. I added an Execute Process task to run the WinZip and I pass the file name in the command line arguments. There are other products that can do the same I believe, such as PKZip.|||We have built task for archiving in Zip, GZip, BZip2, Unix, Tar. We also have task for secure FTP (SFTP). You may give it a try here: http://www.cozyroc.com/

Regards,
Ivan

Need "commercial grade" FileWatcher task

Does anyone know of such a task than runs under both x86 and x64? (I know a FileWatcher add-in but it was built using Beta verisons of VS.NET)

TIA,

barkingdog

I would suggest using the WMI Event Watcher task - see http://msdn2.microsoft.com/en-us/library/ms141130(SQL.90).aspx

Donald